﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text;

public partial class DataServiceForm : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["userID"] == null)
        {
            //Page.ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script language=javascript>window.setTimeout(window.location.href = 'Default.aspx' ,5000);</script>");
            // Response.Redirect("Default.aspx");
        }
        Session["where"] = this.ToString();
        string app = Request.Form["app"];
        if (app == "contact")
        {
            contact();
        }
        else if (app == "calendar")
        {
            calendar();
        }
        else if (app == "signup")
        {
            signup();
        }
        else if (app == "profile")
        {
            profile();
        }
        else if (app == "calendar2")
        {
            calendar2();
        }
        else if (app == "login")
        {
            login();
        }
        else if (app == "group")
        {
            group();
        }
        else if (app == "group_calendar")
        {
            groupCalendar();
        }
        else if (app == "push")
        {
            push();
        }
        else if (app == "check_session")
        {
            string result = "";
            if (Session["userID"].ToString() == "")
                result += "<msg>error</msg>";
            else
            {
                result += "<msg>pass</msg>";
                result += "<accountid>" + Session["userID"] + "</accountid>";
                result += "<accountname>" + Session["userName"] + "</accountname>";
            }
            Response.Write(result);
        }
        else if (app == "logout")
        {
            Session["userID"] = "";
            Session["userName"] = "";
        }
    }
    #region appContact
    protected void contact()
    {
        string func = Request.Form["func"];
        if (func == "query")
            contactQuery();
        else if (func == "check")
            contactPreQuery();
        else if (func == "insert")
            contactInsert();
        else if (func == "delete")
            contactDelete();
        else if (func == "update")
            contactUpdate();
    }
    protected void contactQuery()
    {
        DbConnect conn = new DbConnect();
        /*conn.query = "SELECT a.ContactName, a.IdentifyId, b.Username, a.ContactPhone FROM Contacts	a, Users b " +
        "WHERE a.UserId='" + Session["userID"] + "' and a.IdentifyId = b.UserId";*/
        conn.query = "SELECT * FROM Contacts WHERE AccountID = '{" + Session["userID"] + "}'";
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void contactPreQuery()
    {
        //PreCheck for Insert Contact
        string name = Request.Form["name"];
        string domain = Request.Form["domain"];
        DbConnect preConn = new DbConnect();
        preConn.query = "SELECT LocalID FROM Contacts WHERE Email = '" + name + "@" + domain + "' " +
            "AND AccountID = '{" + Session["userID"] + "}'";
        string result = preConn.GetResult2();
        if (result == String.Empty)
            Response.Write("<conn name='msg'>true</conn>");
        else
            Response.Write("<conn name='msg'>false<conn>");
        Response.Write(result);
    }
    protected void contactInsert()
    {
        string name = Request.Form["name"];
        string domain = Request.Form["domain"];       
        DbConnect conn = new DbConnect();
        //conn.query = "IF EXISTS ( select Username from Users Where Username = '" + user + "')" +
         //   "BEGIN INSERT INTO Contacts values('" + Session["userID"] + "','" + checkid + "','"+contactname+"','','','','','','','','') END";
        conn.query = "INSERT INTO [Contacts] ([AccountID],[Email]) " +
        "VALUES('{" + Session["userID"] + "}', '" + name + "@" + domain + "')";
        conn.Update();
        //Check for IdenID
        DbConnect preConn = new DbConnect();
        preConn.query = "SELECT LocalID FROM Accounts WHERE Email = '" + name + "@" + domain + "'";
        string result = preConn.GetResult2();
        if (result == String.Empty)
        {
            conn.query = "UPDATE Contacts SET IdenID ='{00000000-0000-0000-0000-000000000000}' WHERE " +
            "AccountID = '" + Session["userID"] + "' AND Email = '" + name + "@" + domain + "'";
            conn.Update();
        }
        else
        {
            conn.query = "UPDATE Contacts SET IdenID ='" + result + "' WHERE " +
            "AccountID = '" + Session["userID"] + "' AND Email = '" + name + "@" + domain + "'";
            conn.Update();
        }

    }
    protected void contactDelete()
    {
        string email = Request.Form["email"];
        DbConnect conn = new DbConnect();
        conn.query = "DELETE FROM [Contacts] WHERE " +
            "Email = '" + email + "' AND AccountID = '{" + Session["userID"] + "}'";
        conn.Update();
        Response.Write("<conn name='msg'>Delete Success</conn>");
    }
    protected void contactUpdate()
    {
        /*string checkid = string.Empty;
        string user = Request.Form["user"];
        string contactname = Request.Form["contactname"];
        string phonenumber = Request.Form["phonenumber"];
        checkid = contactPreQuery(user);
        DbConnect conn = new DbConnect();
        conn.query = "UPDATE Contacts " +
                     "SET ContactName = '"+ contactname +"', ContactPhone = '"+ phonenumber +"' " +
                     "WHERE UserId = '"+ Session["userID"] +"' and IdentifyId = '"+ checkid +"'";
        conn.Update();
        Response.Write("<conn name='msg'>"+conn.query+"</conn>");*/
        string firstname = Request.Form["firstname"]; if (Request.Form["firstname"] == "") firstname = "N/A";
        string lastname = Request.Form["lastname"]; if (Request.Form["lastname"] == "") lastname = "N/A";
        string nickname = Request.Form["nickname"]; if (Request.Form["nickname"] == "") nickname = "N/A";
        string jobtitle = Request.Form["jobtitle"]; if (Request.Form["jobtitle"] == "") jobtitle = "N/A";
        string officephone = Request.Form["officephone"]; if (Request.Form["officephone"] == "") officephone = "N/A";
        string mobilephone = Request.Form["mobilephone"]; if (Request.Form["mobilephone"] == "") mobilephone = "N/A";
        string officefax = Request.Form["officefax"]; if (Request.Form["officefax"] == "") officefax = "N/A";
        string department = Request.Form["department"]; if (Request.Form["department"] == "") department = "N/A";
        string email = Request.Form["email"]; if (Request.Form["email"] == "") email = "N/A";
        string webpage = Request.Form["webpage"]; if (Request.Form["webpage"] == "") webpage = "N/A";
        string address = Request.Form["address"]; if (Request.Form["address"] == "") address = "N/A";
        string city = Request.Form["city"]; if (Request.Form["city"] == "") city = "N/A";
        string stateregion = Request.Form["stateregion"]; if (Request.Form["stateregion"] == "") stateregion = "N/A";
        string postalcode = Request.Form["postalcode"]; if (Request.Form["postalcode"] == "") postalcode = "N/A";
        string country = Request.Form["country"]; if (Request.Form["country"] == "") country = "N/A";
        string comments = Request.Form["comments"]; if (Request.Form["comments"] == "") comments = "N/A";

        DbConnect conn = new DbConnect();
        conn.query = "UPDATE Contacts SET FirstName = '" + firstname + "', LastName = '" + lastname +"', " +
            "NickName = '" + nickname + "', JobTitle = '" + jobtitle + "', OfficePhone = '" + officephone + "', " +
            "MobilePhone = '" + mobilephone + "', OfficeFax = '" + officefax + "', Department = " +
            "'" + department + "', WebPage = '" + webpage + "', Address = '" + address + "', " +
            "City = '" + city + "', StateRegion = '" + stateregion + "', PostalCode = '" + postalcode + "', " +
            "Country = '" + country + "', Comments = '" + comments + "' WHERE Email = '" + email + "' AND "+
            "AccountID = '{" + Session["userID"] + "}'";
        conn.Update();
    }
    #endregion

    #region appCalendar
    protected void calendar()
    {
        string func = Request.Form["func"];
        if (func == "query")
        {
            calendarQuery();
        }
        else if (func == "insert")
        {
            calendarInsert();
        }
        else if (func == "update")
        {
            calendarUpdate();
        }
        else if (func == "delete")
        {
            calendarDelete();
        }
        else if (func == "swap")
        {
            calendarSwap();
        }
    }
    protected void calendarQuery()
    {
        string date = Request.Form["date"];
        int month = System.Convert.ToInt32(Request.Form["month"], 10) + 1;
        string year = Request.Form["year"];
        DbConnect conn = new DbConnect();
        conn.query = "SELECT datepart(dd,Date) as date,datepart(mm,Date) as month,"+
	    "datepart(yyyy,Date) as year,Time_ID,[Subject],[Desc]"+ 
        "FROM Calendars WHERE User_ID ="+Session["userID"]+
        " and datepart(yyyy,Date) = "+year+" and datepart(mm,Date) = "+month+
        " and datepart(dd,Date) = "+date+"";
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void calendarInsert()
    {
        string date = Request.Form["date"];
        int month = System.Convert.ToInt32(Request.Form["month"], 10)+1;
        string year = Request.Form["year"];
        string time = Request.Form["time"];
        string subject = Request.Form["subject"];
        string desc = Request.Form["desc"];
        DbConnect conn = new DbConnect();
        conn.query = "INSERT INTO Calendars values ('"+Session["userID"]+"','"+time+"','"+month+"/"+date+"/"+year+"','"+subject+"','"+desc+"')";
        conn.Update();
    }
    protected void calendarUpdate()
    {
        string date = Request.Form["date"];
        int month = System.Convert.ToInt32(Request.Form["month"], 10) + 1;
        string year = Request.Form["year"];
        string time = Request.Form["time"];
        string subject = Request.Form["subject"];
        string desc = Request.Form["desc"];
        DbConnect conn = new DbConnect();
        conn.query = "UPDATE Calendars "+ 
        "SET Subject = '" + subject + "', [Desc] = '" + desc + "' "+
        "WHERE User_ID = '" + Session["userID"] + "' and Time_ID = '" + time + "' and "+
	    "datepart(yyyy,Date) = '" + year + "' and datepart(mm,Date) = '" + month + "' and "+ 
	    "datepart(dd,Date) = '" + date + "'";
        conn.Update();
    }

    protected void calendarDelete()
    {
        string date = Request.Form["date"];
        int month = System.Convert.ToInt32(Request.Form["month"], 10)+1;
        string year = Request.Form["year"];
        string time = Request.Form["time"];
        DbConnect conn = new DbConnect();
        conn.query = "DELETE FROM Calendars WHERE User_ID = '" + Session["userID"] + "' and "+
		"Time_ID = '" + time + "' and datepart(yyyy,Date) = '" + year + "' and "+
		"datepart(mm,Date) = '" + month + "' and datepart(dd,Date) = '" + date + "'";
        conn.Update();
    }
    protected void calendarSwap()
    {
        string date = Request.Form["date"];
        int month = System.Convert.ToInt32(Request.Form["month"], 10) + 1;
        string year = Request.Form["year"];
        string time1 = Request.Form["time1"];
        string time2 = Request.Form["time2"];
        string carID1 = string.Empty;
        string carID2 = string.Empty;
        DbConnect conn = new DbConnect();
        //Select calendarID1
        conn.query = "SELECT Calendar_ID FROM Calendars WHERE User_ID = '"+Session["userID"]+"'"+ 
        " and Time_ID = '"+time1+"' and DATEPART(month, Date) = '"+month+"' and"+
		" DATEPART(day, Date) = '"+date+"' and DATEPART(year, Date) = '"+year+"'";
        carID1 = conn.GetResult2();
        //Select calendarID2
        conn.query = "SELECT Calendar_ID FROM Calendars WHERE User_ID = '" + Session["userID"] + "'" +
        " and Time_ID = '" + time2 + "' and DATEPART(month, Date) = '" + month + "' and" +
        " DATEPART(day, Date) = '" + date + "' and DATEPART(year, Date) = '" + year + "'";
        carID2 = conn.GetResult2();
        //Update 1to2
        conn.query = "UPDATE Calendars SET Time_ID='" + time1 + "' WHERE Calendar_ID = '" +carID2 + "'";
        conn.Update();
        //Update 2to1
        conn.query = "UPDATE Calendars SET Time_ID='" + time2 + "' WHERE Calendar_ID = '" + carID1 + "'";
        conn.Update();
    }
    #endregion

    #region appSignup
    protected void signup()
    {
        string func = Request.Form["func"];
        if (func == "insert")
        {
            signupInsert();
        }
        else if (func == "updateiden")
        {
            updateIden();
        }
        else if (func == "query")
        {
            preSignup();
        }
    }
    protected void preSignup()
    {
        string email = Request.Form["email"];
        DbConnect conn = new DbConnect();
        conn.query = "SELECT * FROM Accounts WHERE Email = '" + email + "'";
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void signupInsert()
    {
        string id = Request.Form["id"];
        string pass = Request.Form["pass"];
        string firstname = Request.Form["firstname"];
        string lastname = Request.Form["lastname"];
        string country = Request.Form["country"];
        string city = Request.Form["city"];
        string postalcode = Request.Form["postalcode"];
        string email = Request.Form["email"];
        string phone = Request.Form["phone"];
        DbConnect conn = new DbConnect();
        conn.query = "INSERT INTO [Accounts] " +
           "([AccountName], [AccountPassword], [FirstName], [LastName], [Email], [City] ,[PostalCode]" +
           ", [Country], [MobilePhone]) VALUES ('" + id + "', '" + pass + "', '" + firstname + "', " +
           "'" + lastname + "', '" + email + "', '" + city + "', '" + postalcode + "', '" + country + "', " +
           "'" + phone + "')";
        conn.Update();
    }
    protected void updateIden()
    {
        string email = Request.Form["email"];
        DbConnect conn = new DbConnect();
        conn.query = "UPDATE Contacts SET IdenID = (SELECT LocalID FROM Accounts WHERE Email = " +
        "'" + email + "')WHERE Email ='" + email + "'";
        conn.Update();
    }
    #endregion

    #region appProfile
    protected void profile()
    {
        string func = Request.Form["func"];
        if (func == "query")
        {
            profileQuery();
        }
        else if (func == "update")
        {
            profileUpdate();
        }
    }
    protected void profileQuery()
    {
        DbConnect conn = new DbConnect();
        conn.query = "SELECT * FROM Accounts WHERE LocalID = '" + Session["userID"] + "'";
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void profileUpdate()
    {
        string firstname = Request.Form["firstname"]; if (Request.Form["firstname"] == "") firstname = "N/A";
        string lastname = Request.Form["lastname"]; if (Request.Form["lastname"] == "") lastname = "N/A";
        string nickname = Request.Form["nickname"]; if (Request.Form["nickname"] == "") nickname = "N/A";
        string mobilephone = Request.Form["mobilephone"]; if (Request.Form["mobilephone"] == "") mobilephone = "N/A";
        string email = Request.Form["email"]; if (Request.Form["email"] == "") email = "N/A";
        string webpage = Request.Form["webpage"]; if (Request.Form["webpage"] == "") webpage = "N/A";
        string address = Request.Form["address"]; if (Request.Form["address"] == "") address = "N/A";
        string city = Request.Form["city"]; if (Request.Form["city"] == "") city = "N/A";
        string stateregion = Request.Form["stateregion"]; if (Request.Form["stateregion"] == "") stateregion = "N/A";
        string region = Request.Form["region"]; if (Request.Form["region"] == "") region = "N/A";
        string postalcode = Request.Form["postalcode"]; if (Request.Form["postalcode"] == "") postalcode = "N/A";
        string country = Request.Form["country"]; if (Request.Form["country"] == "") country = "N/A";
        DbConnect conn = new DbConnect();
        conn.query = "UPDATE Accounts SET FirstName = '" + firstname + "', LastName = '" + lastname + "', " +
            "NickName = '" + nickname + "', MobilePhone = '" + mobilephone + "', " +
            "WebPage = '" + webpage + "', Address = '" + address + "', City = '" + city + "', " +
            "StateRegion = '" + stateregion + "', Region = '" + region + "', " +
            "PostalCode = '" + postalcode + "', Country = '" + country + "' "+
            "WHERE LocalID = '" + Session["userID"] + "'";
        conn.Update();
    }
    #endregion

    #region appCalendar2
    protected void calendar2()
    {
        string func = Request.Form["func"];
        if (func == "query")
        {
            calendar2Query();
        }
        else if (func == "insert")
        {
            calendar2Insert();
        }
        else if (func == "del")
        {
            calendar2Delete();
        }
        else if (func == "edit1")
        {
            calendar2Edit1();
        }
        else if (func == "edit2")
        {
            calendar2Edit2();
        }
        else if (func == "group_select")
        {
            groupSelect();
        }
        else if (func == "group_query")
        {
            groupQuery();
        }
        else if (func == "contact_query")
        {
            contactList();
        }
        else if (func == "insert_asso")
        {
            insertAsso();
        }
    }
    protected void calendar2Query()
    {
        int date = System.Convert.ToInt32(Request.Form["date"], 10);
        int month = System.Convert.ToInt32(Request.Form["month"], 10) + 1;
        int year = System.Convert.ToInt32(Request.Form["year"], 10);
        DbConnect conn = new DbConnect();
        conn.query = "SELECT	datepart(dd, [Start]) as sDate, datepart(mm, [Start]) as sMonth, " +
        "datepart(yyyy, [Start]) as sYear, datepart(dd, [End]) as eDate, " +
        "datepart(mm, [End]) as eMonth, datepart(yyyy, [End]) as eYear, " +
        "datepart(hh,[Start]) as sHour, datepart(mi,[Start]) as sMin, " +
        "datepart(ss,[Start]) as sSec, datepart(hh,[End]) as eHour, " +
        "datepart(mi,[End]) as eMin, datepart(ss,[End]) as eSec, " +
        "[Start], [End], Duration, Location, [Subject], Comments, LocalID " +
        "FROM Appointments " +
        "WHERE	datepart(yyyy, [Start]) = '" + year + "' AND " +
        "datepart(mm, [Start]) = '" + month + "' AND " +
        "datepart(dd, [Start]) <= '" + date + "' AND " +
        "datepart(yyyy, [End]) = '" + year + "' AND " +
        "datepart(mm, [End]) = '" + month + "' AND " +
        "datepart(dd, [End]) >= '" + date + "' AND " +
        "AccountID = '" + Session["userID"] + "'";
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void calendar2Insert()
    {
        string date = Request.Form["date"];
        int month = System.Convert.ToInt32(Request.Form["month"], 10) + 1;
        string year = Request.Form["year"];
        string start = Request.Form["start"];
        string end = Request.Form["end"];
        string duration = Request.Form["duration"];
        string subject = Request.Form["subject"];
        string comments = Request.Form["comments"];
        string allday = Request.Form["allday"];
        string ii = year + "-" + month.ToString() + "-" + date + " " + start;
        string mm = year + "-" + month.ToString() + "-" + date + " " + end;
        DbConnect conn = new DbConnect();
        conn.query = "INSERT INTO [Appointments] " +
           "([AccountID], [Subject], [Start], [End], [Duration], [Comments]) VALUES " +
           "('" + Session["userID"] + "', '" + subject + "', '" + ii + "', '" + mm + "', " +
           "'" + duration + "', '" + comments + "')";
        conn.Update();
    }
    protected void calendar2Delete()
    {
        string id = Request.Form["id"];
        DbConnect conn = new DbConnect();
        conn.query = "DELETE FROM Appointments WHERE LocalID = '" + id + "'";
        conn.Update();
    }
    protected void calendar2Edit1()
    {
        string id = Request.Form["id"];
        string subject = Request.Form["subject"];
        string location = Request.Form["location"];
        string comments = Request.Form["comments"];
        DbConnect conn = new DbConnect();
        conn.query = "UPDATE [Appointments] " +
                     "SET [Subject]= '" + subject + "', [Location] = '" + location + "', " +
		             "[Comments] = '" + comments + "' WHERE LocalID = '" + id + "'";
        conn.Update();
    }
    protected void calendar2Edit2()
    {
        string date = Request.Form["date"];
        int month = System.Convert.ToInt32(Request.Form["month"], 10) + 1;
        string year = Request.Form["year"];
        string id = Request.Form["id"];
        string subject = Request.Form["subject"];
        string start = Request.Form["start"];
        string end = Request.Form["end"];
        string ii = year + "-" + month.ToString() + "-" + date + " " + start;
        string mm = year + "-" + month.ToString() + "-" + date + " " + end;
        DbConnect conn = new DbConnect();
        conn.query = "UPDATE [Appointments] " +
                     "SET [Start] = '" + ii + "', [End] = '" + mm + "' " +
                     "WHERE LocalID = '" + id + "'";
        conn.Update();
    }
    protected void groupSelect()
    {
        DbConnect conn = new DbConnect();
        conn.query = "SELECT Groups.LocalID, Groups.GroupName FROM [Groups] INNER JOIN Contacts_Group " +
        "ON Groups.LocalID = Contacts_Group.GroupID WHERE Contacts_Group.AccountID = " +
        "'" + Session["userID"] + "' ";
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void groupQuery()
    {
        int date = System.Convert.ToInt32(Request.Form["date"], 10);
        int month = System.Convert.ToInt32(Request.Form["month"], 10) + 1;
        int year = System.Convert.ToInt32(Request.Form["year"], 10);
        string gid = Request.Form["gid"];
        if (gid == "" || gid == null)
        {
            Response.Write("<count name='row'>0</count>");
        }
        else
        {
            DbConnect conn = new DbConnect();
            conn.query = "SELECT	datepart(dd, [Start]) as sDate, datepart(mm, [Start]) as sMonth, " +
            "datepart(yyyy, [Start]) as sYear, datepart(dd, [End]) as eDate, " +
            "datepart(mm, [End]) as eMonth, datepart(yyyy, [End]) as eYear, " +
            "datepart(hh,[Start]) as sHour, datepart(mi,[Start]) as sMin, " +
            "datepart(ss,[Start]) as sSec, datepart(hh,[End]) as eHour, " +
            "datepart(mi,[End]) as eMin, datepart(ss,[End]) as eSec, " +
            "[Start], [End], Duration, Location, [Subject], Comments, LocalID " +
            "FROM Appointments_Group " +
            "WHERE	datepart(yyyy, [Start]) = '" + year + "' AND " +
            "datepart(mm, [Start]) = '" + month + "' AND " +
            "datepart(dd, [Start]) <= '" + date + "' AND " +
            "datepart(yyyy, [End]) = '" + year + "' AND " +
            "datepart(mm, [End]) = '" + month + "' AND " +
            "datepart(dd, [End]) >= '" + date + "' AND " +
            "GroupID = '" + gid + "'";
            string result = conn.GetResult();
            Response.Write(result);
        }
    }
    protected void contactList()
    {
        DbConnect conn = new DbConnect();
        /*conn.query = "SELECT a.ContactName, a.IdentifyId, b.Username, a.ContactPhone FROM Contacts	a, Users b " +
        "WHERE a.UserId='" + Session["userID"] + "' and a.IdentifyId = b.UserId";*/
        conn.query = "SELECT *, Accounts.AccountName FROM Contacts INNER JOIN Accounts " +
        "ON Accounts.LocalID = Contacts.IdenID WHERE AccountID = '" + Session["userID"] + "'";
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void insertAsso()
    {
        string conid = Request.Form["conid"];
        string appid = Request.Form["appid"];
        DbConnect conn = new DbConnect();
        conn.query = "INSERT INTO [ContactAppointment] ([ContactID], [AppointmentID]) " +
        "(SELECT '" + conid + "','" + appid + "' " +
        "WHERE not exists (SELECT * FROM [ContactAppointment] WHERE ContactID = " +
        "'" + conid + "' AND AppointmentID = '" + appid + "' ))";
        conn.Update();
    }
    #endregion

    #region appLogin
    protected void login()
    {
        string func = Request.Form["func"];
        if (func == "login")
        {
            loginQuery();
        }
        else if (func == "auth")
        {
            loginAuth();
        }
    }
    protected void loginQuery()
    {
        string id = Request.Form["id"];
        string pass = Request.Form["pass"];
        DbConnect conn = new DbConnect();
        conn.query = "SELECT * FROM Accounts WHERE AccountName = '" + id + "' AND " +
            "AccountPassword = '" + pass + "'" ; 
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void loginAuth()
    {
        string id = Request.Form["id"];
        string name = Request.Form["name"];
        Session["userID"] = id;
        Session["userName"] = name;
    }
    #endregion

    #region appGroup

    protected void group()
    {
        string func = Request.Form["func"];
        if (func == "m_query")
        {
            myQuery();
        }
        else if (func == "a_query")
        {
            allQuery();
        }
        else if (func == "l_query")
        {
            listQuery();
        }
        else if (func == "member_query")
        {
            memberQuery();
        }
        else if (func == "pre_build")
        {
            preBuild();
        }
        else if (func == "build_group")
        {
            buildGroup();
        }
        else if (func == "pre_join")
        {
            preJoin();
        }
        else if (func == "join_group")
        {
            joinGroup();
        }
        else if (func == "del_group")
        {
            delGroup();
        }
        else if (func == "edit_desc")
        {
            editDesc();
        }
        else if (func == "leave_group")
        {
            leaveGroup();
        }
    }
    protected void myQuery()
    {
        DbConnect conn = new DbConnect();
        conn.query = "SELECT [GroupName], Groups.LocalID, Groups.Description, Accounts.AccountName " +
            "FROM Groups INNER JOIN Accounts ON Groups.AccountID = Accounts.LocalID " +
            "WHERE AccountID = '" + Session["userID"] + "'";
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void allQuery()
    {
        DbConnect conn = new DbConnect();
        conn.query = "SELECT [GroupName], Groups.LocalID, Groups.Description, Accounts.AccountName " +
            "FROM Groups  INNER JOIN Contacts_Group ON Groups.LocalID = Contacts_Group.GroupID " +
		    "INNER JOIN Accounts ON Groups.AccountID = Accounts.LocalID " +  
            "WHERE Contacts_Group.AccountID = '" + Session["userID"] + "'";
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void listQuery()
    {
        DbConnect conn = new DbConnect();
        conn.query = "SELECT Groups.GroupName, Groups.Description, Groups.LocalID, COUNT(*) as Member " +
        "FROM [Groups] INNER JOIN Contacts_Group " +
        "ON Groups.LocalID = Contacts_Group.GroupID GROUP BY Groups.GroupName, Groups.Description, " +
        "Groups.LocalID";
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void memberQuery()
    {
        string gid = Request.Form["gid"];
        DbConnect conn = new DbConnect();
        conn.query = "SELECT AccountName FROM Accounts INNER JOIN Contacts_Group ON Accounts.LocalID " +
        "= Contacts_Group.AccountID WHERE Contacts_Group.GroupID = '" + gid + "' AND " +
	    "Contacts_Group.AccountID != '" + Session["userID"] + "'";
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void preBuild()
    {
        string name = Request.Form["name"];
        DbConnect conn = new DbConnect();
        conn.query = "SELECT * FROM Groups WHERE GroupName = '" + name + "'";
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void buildGroup()
    {
        string name = Request.Form["name"];
        string desc = Request.Form["desc"];
        DbConnect conn = new DbConnect();
        conn.query = "INSERT INTO Groups (AccountID, GroupName, Description) VALUES ('" + Session["userID"] + "', " +
            "'" + name + "','" + desc + "')";
        conn.Update();
        DbConnect conn2 = new DbConnect();
        conn2.query = "INSERT INTO [Contacts_Group] " +
        "(AccountID, GroupID) select '" + Session["userID"] + "', Groups.LocalID " +
        "FROM Groups WHERE GroupName = '" + name + "'";
        conn2.Update();
    }
    protected void preJoin()
    {
        string gid = Request.Form["gid"];
        DbConnect conn = new DbConnect();
        conn.query = "SELECT * FROM Contacts_Group WHERE GroupID = '" + gid + "' AND " +
        "AccountID = '" + Session["userID"] +"'";
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void joinGroup()
    {
        string gid = Request.Form["gid"];
        DbConnect conn = new DbConnect();
        conn.query = "INSERT INTO [Contacts_Group] " +
        "(AccountID, GroupID) VALUES ('" + Session["userID"] + "','" + gid + "')";
        conn.Update();
    }
    protected void delGroup()
    {
        string gid = Request.Form["gid"];
        DbConnect conn = new DbConnect();
        conn.query = "DELETE FROM Groups WHERE LocalID = '" + gid +"'";
        conn.Update();
    }
    protected void editDesc()
    {
        string gid = Request.Form["gid"];
        string desc = Request.Form["desc"];
        DbConnect conn = new DbConnect();
        conn.query = "UPDATE Groups SET Description = '" + desc + "' WHERE LocalID = '" + gid + "'";
        conn.Update();
    }
    protected void leaveGroup()
    {
        string gid = Request.Form["gid"];
        DbConnect conn = new DbConnect();
        conn.query = "DELETE FROM Contacts_Group WHERE GroupID = '" + gid + "' " +
        "AND AccountID = '" + Session["userID"] + "' AND " +
        "(GroupID not in (SELECT LocalID FROM Groups WHERE AccountID = '" + Session["userID"] + "'))";
        conn.Update();
    }
    #endregion

    #region appGroupCalendar
    protected void groupCalendar()
    {
        string func = Request.Form["func"];
        if (func == "query")
        {
            gcQuery();
        }
        else if (func == "insert")
        {
            gcInsert();
        }
        else if (func == "del")
        {
            gcDelete();
        }
        else if (func == "edit1")
        {
            gcEdit1();
        }
        else if (func == "edit2")
        {
            gcEdit2();
        }
        else if (func == "group_contact_query")
            groupContactQuery();
        else if (func == "group_insert_asso")
            groupInsertAsso();
    }
    protected void gcQuery()
    {
        int date = System.Convert.ToInt32(Request.Form["date"], 10);
        int month = System.Convert.ToInt32(Request.Form["month"], 10) + 1;
        int year = System.Convert.ToInt32(Request.Form["year"], 10);
        string gid = Request.Form["gid"];
        DbConnect conn = new DbConnect();
        conn.query = "SELECT	datepart(dd, [Start]) as sDate, datepart(mm, [Start]) as sMonth, " +
        "datepart(yyyy, [Start]) as sYear, datepart(dd, [End]) as eDate, " +
        "datepart(mm, [End]) as eMonth, datepart(yyyy, [End]) as eYear, " +
        "datepart(hh,[Start]) as sHour, datepart(mi,[Start]) as sMin, " +
        "datepart(ss,[Start]) as sSec, datepart(hh,[End]) as eHour, " +
        "datepart(mi,[End]) as eMin, datepart(ss,[End]) as eSec, " +
        "[Start], [End], Duration, Location, [Subject], Comments, LocalID " +
        "FROM Appointments_Group " +
        "WHERE	datepart(yyyy, [Start]) = '" + year + "' AND " +
        "datepart(mm, [Start]) = '" + month + "' AND " +
        "datepart(dd, [Start]) <= '" + date + "' AND " +
        "datepart(yyyy, [End]) = '" + year + "' AND " +
        "datepart(mm, [End]) = '" + month + "' AND " +
        "datepart(dd, [End]) >= '" + date + "' AND " +
        "GroupID = '" + gid + "'";
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void gcInsert()
    {
        string date = Request.Form["date"];
        int month = System.Convert.ToInt32(Request.Form["month"], 10) + 1;
        string year = Request.Form["year"];
        string start = Request.Form["start"];
        string end = Request.Form["end"];
        string duration = Request.Form["duration"];
        string subject = Request.Form["subject"];
        string comments = Request.Form["comments"];
        string allday = Request.Form["allday"];
        string ii = year + "-" + month.ToString() + "-" + date + " " + start;
        string mm = year + "-" + month.ToString() + "-" + date + " " + end;
        string gid = Request.Form["gid"];
        DbConnect conn = new DbConnect();
        conn.query = "INSERT INTO [Appointments_Group] ([GroupID], [Subject], [Start], [End], " +
        "[Duration], [Comments]) VALUES ('" + gid + "', '" + subject + "', '" + ii + "', " + 
		"'" + mm + "', '" + duration + "', '" + comments + "')";
        conn.Update();
    }
    protected void gcDelete()
    {
        string id = Request.Form["id"];
        DbConnect conn = new DbConnect();
        conn.query = "DELETE FROM Appointments_Group WHERE LocalID = '" + id + "'";
        conn.Update();
    }
    protected void gcEdit1()
    {
        string id = Request.Form["id"];
        string subject = Request.Form["subject"];
        string location = Request.Form["location"];
        string comments = Request.Form["comments"];
        DbConnect conn = new DbConnect();
        conn.query = "UPDATE [Appointments_Group] " +
                     "SET [Subject]= '" + subject + "', [Location] = '" + location + "', " +
                     "[Comments] = '" + comments + "' WHERE LocalID = '" + id + "'";
        conn.Update();
    }
    protected void gcEdit2()
    {
        string date = Request.Form["date"];
        int month = System.Convert.ToInt32(Request.Form["month"], 10) + 1;
        string year = Request.Form["year"];
        string id = Request.Form["id"];
        string subject = Request.Form["subject"];
        string start = Request.Form["start"];
        string end = Request.Form["end"];
        string ii = year + "-" + month.ToString() + "-" + date + " " + start;
        string mm = year + "-" + month.ToString() + "-" + date + " " + end;
        DbConnect conn = new DbConnect();
        conn.query = "UPDATE [Appointments_Group] " +
                     "SET [Start] = '" + ii + "', [End] = '" + mm + "' " +
                     "WHERE LocalID = '" + id + "'";
        conn.Update();
    }
    protected void groupContactQuery()
    {
        string gid = Request.Form["gid"];
        DbConnect conn = new DbConnect();
        /*conn.query = "SELECT a.ContactName, a.IdentifyId, b.Username, a.ContactPhone FROM Contacts	a, Users b " +
        "WHERE a.UserId='" + Session["userID"] + "' and a.IdentifyId = b.UserId";*/
        conn.query = "SELECT Contacts_Group.LocalID, Accounts.AccountName FROM Contacts_Group " +
        "INNER JOIN Accounts " +  
        "ON Accounts.LocalID = Contacts_Group.AccountID WHERE GroupID = '" + gid + "' AND " +
        "Accounts.LocalID != '" + Session["userID"] + "'";
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void groupInsertAsso()
    {
        string congid = Request.Form["congid"];
        string appgid = Request.Form["appgid"];
        DbConnect conn = new DbConnect();
        conn.query = "INSERT INTO [ContactAppointment_Group] ([ContactID], [AppointmentID]) " +
        "(SELECT '" + congid + "','" + appgid + "' " +
        "WHERE not exists (SELECT * FROM [ContactAppointment_Group] WHERE ContactID = " +
        "'" + congid + "' AND AppointmentID = '" + appgid + "' ))";
        conn.Update();
    }
    #endregion

    #region appPush
    protected void push()
    {
        string func = Request.Form["func"];
        if (func == "con_app_query")
            contactAppointmentQuery();
        else if (func == "asso_del")
            assoDelete();
        else if (func == "asso_push")
            assoPush();
        else if (func == "push_query")
            pushQuery();
        else if (func == "push_accept")
            pushAccept();
        else if (func == "push_cancel")
            pushDelete();
        else if (func == "group_con_app_query")
            groupContactAppointmentQuery();
    }
    protected void contactAppointmentQuery()
    {
        DbConnect conn = new DbConnect();
        conn.query = "SELECT ContactAppointment.LocalID, Contacts.Email, ContactAppointment.ContactID, " +
        "ContactAppointment.AppointmentID, Appointments.Subject, Appointments.Start, Appointments.[End], " +
        "Appointments.Duration, Appointments.Location, Appointments.Comments, Contacts.IdenID, " +
        "datepart(dd, [Start]) as sDate, datepart(mm, [Start]) as sMonth, " + 
        "datepart(yyyy, [Start]) as sYear, datepart(dd, [End]) as eDate, " +  
        "datepart(mm, [End]) as eMonth, datepart(yyyy, [End]) as eYear " +
        "FROM ContactAppointment, Contacts, Appointments " +
        "WHERE ContactID = Contacts.LocalID AND AppointmentID = Appointments.LocalID AND " +
        "Contacts.AccountID = '" + Session["userID"] + "' AND " +
        "Appointments.AccountID = '" + Session["userID"] + "' "; 
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void assoDelete()
    {
        string id = Request.Form["id"];
        DbConnect conn = new DbConnect();
        conn.query = "DELETE FROM ContactAppointment WHERE LocalID = '" + id + "'";
        conn.Update();
    }
    protected void assoPush()
    {
        string id = Request.Form["id"];
        string start = Request.Form["start"];
        string end = Request.Form["end"];
        string conid = Request.Form["conid"];
        string subject = Request.Form["subject"];
        string location = Request.Form["location"];
        string comments = Request.Form["comments"];
        string duration = Request.Form["duration"];
        DbConnect conn = new DbConnect();
        conn.query = "INSERT INTO [P_Push_App] ([Sender],[Receiver],[Subject],[Location] " +
        ",[Duration],[Comments],[Start],[END]) SELECT '" + Session["userID"] +"', " +
        "(SELECT Accounts.LocalID FROM Accounts INNER JOIN Contacts ON Accounts.LocalID " +
        "= Contacts.IdenID WHERE Contacts.LocalID = '" + conid +"') " +
        ", '" + subject + "',  '" + location + "', '" + duration + "', '" + comments +"', " +
        "(SELECT Start FROM Appointments INNER JOIN ContactAppointment ON " +
        "ContactAppointment.AppointmentID = Appointments.LocalID WHERE " +
        "ContactAppointment.LocalID = '" + id + "'), " +
        "(SELECT [End] FROM Appointments INNER JOIN ContactAppointment ON " +
        "ContactAppointment.AppointmentID = Appointments.LocalID WHERE " +
        "ContactAppointment.LocalID = '" + id + "')";  

        conn.Update();
    }
    protected void pushQuery()
    {
        DbConnect conn = new DbConnect();
        conn.query = "SELECT *,datepart(dd, [Start]) as sDate, datepart(mm, [Start]) as sMonth, " + 
        "datepart(yyyy, [Start]) as sYear, datepart(dd, [End]) as eDate, " +  
        "datepart(mm, [End]) as eMonth, datepart(yyyy, [End]) as eYear, AccountName " +
        "FROM [P_Push_App] INNER JOIN Accounts ON Accounts.LocalID = [P_Push_App].Sender " +
        "WHERE Receiver = '" + Session["userID"] + "'";
        string result = conn.GetResult();
        Response.Write(result);
    }
    protected void pushAccept()
    {
        string id = Request.Form["id"];
        DbConnect conn = new DbConnect();
        conn.query = "INSERT INTO [Appointments] ([AccountID],[Subject],[Location],[Start],[End] " +
        ",[Duration],[Comments]) " +
        "SELECT Receiver, [Subject], Location, [Start], [End], Duration, Comments FROM [P_Push_App] " +
        "WHERE [P_Push_App].LocalID = '" + id + "';";
        conn.Update();
        pushDelete();
    }
    protected void pushDelete()
    {
        string id = Request.Form["id"];
        DbConnect conn = new DbConnect();
        conn.query = "DELETE FROM [P_Push_App] WHERE LocalID = '" + id + "'";
        conn.Update();
    }
    protected void groupContactAppointmentQuery()
    {
        string gid = Request.Form["gid"];
        DbConnect conn = new DbConnect();
        conn.query = "SELECT ContactAppointment_Group.LocalID, ContactAppointment_Group.ContactID, " +  
        "ContactAppointment_Group.AppointmentID, Appointments_Group.Subject, " +
		"Appointments_Group.Start, Appointments_Group.[End], " +  
        "Appointments_Group.Duration, Appointments_Group.Location, " + 
		"Appointments_Group.Comments, Contacts_Group.GroupID, " +  
        "datepart(dd, [Start]) as sDate, datepart(mm, [Start]) as sMonth, " +   
        "datepart(yyyy, [Start]) as sYear, datepart(dd, [End]) as eDate, " +   
        "datepart(mm, [End]) as eMonth, datepart(yyyy, [End]) as eYear " +  
        "FROM ContactAppointment_Group, Contacts_Group, Appointments_Group , Accounts " +  
        "WHERE ContactID = Contacts_Group.LocalID AND AppointmentID = Appointments_Group.LocalID AND " +  
        "Contacts_Group.GroupID = '" + gid + "' AND  " + 
        "Appointments_Group.GroupID = '" + gid + "' AND " + 
		"Accounts.LocalID = '" + Session["userID"] + "'";
        string result = conn.GetResult();
        Response.Write(result);
    }
    #endregion
}
